IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ChapterDelete')
	BEGIN
		DROP  Procedure  ChapterDelete
	END

GO

CREATE Procedure ChapterDelete
@ChapterID Int

AS
Declare @SagaID Int 
Select @SagaID = SagaID From Chapter Where ChapterID = @ChapterID

Declare @ViewOrder Int
Select  @ViewOrder = ViewOrder From Chapter Where ChapterID = @ChapterID

-- Delete Related Chapters
Delete  RelatedChapter Where 
RelatedChapterID = @ChapterID OR Chapter_CurrentID = @ChapterID


-- Delete ChapterSection
Delete  ChapterSection 
Where ChapterID = @ChapterID

-- Delete Comments
Delete  CapterComments 
Where ChapterID = @ChapterID

-- Update ViewOrder Ordinal 
Update Chapter 
Set ViewOrder = ViewOrder -1
Where SagaID = @SagaID And ViewOrder >  @ViewOrder

-- Delete Chapter
Delete Chapter
Where ChapterID = @ChapterID

GO